import os
import pymysql
from pymysql.cursors import DictCursor
from dotenv import load_dotenv
from typing import List, Dict, Any, Optional

# 加载环境变量
load_dotenv()

class MySQLManager:
    """MySQL数据库管理类，负责数据库连接和基本操作"""
    
    _instance = None
    
    def __new__(cls):
        # 单例模式
        if cls._instance is None:
            cls._instance = super(MySQLManager, cls).__new__(cls)
            cls._instance._initialize()
        return cls._instance
    
    def _initialize(self):
        # 从环境变量获取数据库配置
        self.db_config = {
            'host': os.getenv('MYSQL_HOST', 'localhost'),
            'port': int(os.getenv('MYSQL_PORT', '3306')),
            'user': os.getenv('MYSQL_USER', 'root'),
            'password': os.getenv('MYSQL_PASSWORD', ''),
            'database': os.getenv('MYSQL_DATABASE', 'rag_system'),
            'charset': 'utf8mb4'
        }
        self.connection = None
        
    def connect(self):
        """建立数据库连接"""
        if self.connection is None or not self.connection.open:
            try:
                self.connection = pymysql.connect(
                    **self.db_config,
                    cursorclass=DictCursor,
                    autocommit=False
                )
                print(f"成功连接到MySQL数据库: {self.db_config['host']}:{self.db_config['port']}")
            except Exception as e:
                print(f"连接到MySQL数据库失败: {e}")
                self.connection = None
        return self.connection
    
    def disconnect(self):
        """关闭数据库连接"""
        if self.connection and self.connection.open:
            try:
                self.connection.close()
                print("MySQL数据库连接已关闭")
            except Exception as e:
                print(f"关闭数据库连接时出错: {e}")
            self.connection = None
    
    def execute_query(self, query: str, params: tuple = None) -> List[Dict[str, Any]]:
        """执行查询语句并返回结果"""
        results = []
        try:
            if not self.connect():
                return results
            
            with self.connection.cursor() as cursor:
                cursor.execute(query, params)
                results = cursor.fetchall()
        except Exception as e:
            print(f"执行查询时出错: {e}")
        return results
    
    def execute_update(self, query: str, params: tuple = None) -> int:
        """执行更新语句并返回影响的行数"""
        affected_rows = 0
        try:
            if not self.connect():
                return affected_rows
            
            with self.connection.cursor() as cursor:
                affected_rows = cursor.execute(query, params)
                self.connection.commit()
        except Exception as e:
            print(f"执行更新时出错: {e}")
            if self.connection:
                self.connection.rollback()
        return affected_rows
    
    def execute_many(self, query: str, params: list) -> int:
        """批量执行SQL语句"""
        affected_rows = 0
        try:
            if not self.connect():
                return affected_rows
            
            with self.connection.cursor() as cursor:
                affected_rows = cursor.executemany(query, params)
                self.connection.commit()
        except Exception as e:
            print(f"批量执行SQL时出错: {e}")
            if self.connection:
                self.connection.rollback()
        return affected_rows
    
    def initialize_database(self):
        """初始化数据库，创建必要的表"""
        try:
            # 首先创建数据库（如果使用root用户）
            if self.db_config['user'] == 'root':
                temp_config = self.db_config.copy()
                temp_config.pop('database')
                temp_conn = pymysql.connect(**temp_config)
                with temp_conn.cursor() as cursor:
                    cursor.execute(f"CREATE DATABASE IF NOT EXISTS {self.db_config['database']} DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;")
                temp_conn.close()
            
            # 创建表结构
            tables = [
                # 订单表
                '''
                CREATE TABLE IF NOT EXISTS orders (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    order_no VARCHAR(50) NOT NULL UNIQUE,
                    customer_name VARCHAR(100) NOT NULL,
                    customer_email VARCHAR(100) NOT NULL,
                    total_amount DECIMAL(10,2) NOT NULL,
                    status VARCHAR(20) NOT NULL DEFAULT 'pending',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                );
                ''',
                # 订单详情表
                '''
                CREATE TABLE IF NOT EXISTS order_items (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    order_id INT NOT NULL,
                    product_name VARCHAR(100) NOT NULL,
                    quantity INT NOT NULL DEFAULT 1,
                    unit_price DECIMAL(10,2) NOT NULL,
                    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
                );
                '''
            ]
            
            for table_sql in tables:
                self.execute_update(table_sql)
                
            print("MySQL数据库初始化完成")
            return True
        except Exception as e:
            print(f"初始化数据库时出错: {e}")
            return False
    
    def insert_initial_data(self):
        """插入初始数据"""
        try:
            # 检查是否已经有数据
            orders_count = len(self.execute_query("SELECT COUNT(*) as count FROM orders;"))
            if orders_count > 0:
                print("数据库中已有数据，跳过初始化数据插入")
                return True
            
            # 插入订单数据
            orders = [
                ('ORD-20240001', '张三', 'zhangsan@example.com', 1599.00, 'completed'),
                ('ORD-20240002', '李四', 'lisi@example.com', 899.50, 'pending'),
                ('ORD-20240003', '王五', 'wangwu@example.com', 2399.00, 'completed'),
            ]
            order_query = """
                INSERT INTO orders (order_no, customer_name, customer_email, total_amount, status)
                VALUES (%s, %s, %s, %s, %s)
            """
            self.execute_many(order_query, orders)
            
            # 获取刚插入的订单ID
            orders_data = self.execute_query("SELECT id, order_no FROM orders;")
            order_map = {order['order_no']: order['id'] for order in orders_data}
            
            # 插入订单项数据
            order_items = [
                # 订单ORD-20240001的商品
                (order_map['ORD-20240001'], '智能手表', 1, 1599.00),
                # 订单ORD-20240002的商品
                (order_map['ORD-20240002'], '蓝牙耳机', 2, 449.75),
                # 订单ORD-20240003的商品
                (order_map['ORD-20240003'], '笔记本电脑', 1, 2399.00),
            ]
            order_item_query = """
                INSERT INTO order_items (order_id, product_name, quantity, unit_price)
                VALUES (%s, %s, %s, %s)
            """
            self.execute_many(order_item_query, order_items)
            
            print("初始化数据插入完成")
            return True
        except Exception as e:
            print(f"插入初始数据时出错: {e}")
            return False

# 创建全局实例
db_manager = MySQLManager()